﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Common;
using DTN.Core.Utilities;
namespace com.dtn.deals
{
    public class tbl_ConponManager : Itbl_ConponManager
    {
        private static Itbl_ConponManager itbl_ConponManager = null;
        private tbl_ConponManager()
        {
        }
        public static Itbl_ConponManager CreateInstance()
        {
            if (itbl_ConponManager == null)
            {
                itbl_ConponManager = new tbl_ConponManager();
            }
            return itbl_ConponManager;
        }
        public int Inserttbl_Conpon(tbl_Conpon tbl_Conpon)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@conponID", SqlDbType.Int),
																new SqlParameter("@conponCode", SqlDbType.VarChar),
                                                                new SqlParameter("@conponName", SqlDbType.NVarChar),

                                                                new SqlParameter("@proId", SqlDbType.Int),
                                                                new SqlParameter("@partnerId", SqlDbType.Int),
                                                                new SqlParameter("@cateId", SqlDbType.Int),
															
                                                                new SqlParameter("@sale", SqlDbType.NVarChar),

																new SqlParameter("@cDate", SqlDbType.DateTime),
																new SqlParameter("@sDateDeal", SqlDbType.DateTime),
                                                                new SqlParameter("@eDateDeal", SqlDbType.DateTime),
                                                                new SqlParameter("@eDateDeal_s", SqlDbType.DateTime),
                                                                new SqlParameter("@eDateDeal_s", SqlDbType.DateTime),
															
                                                                
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_Conpon.conponCode ;
            parameters[2].Value = tbl_Conpon.conponName;
            parameters[3].Value = tbl_Conpon.proId;
            parameters[4].Value = tbl_Conpon.partnerId;
            parameters[5].Value = tbl_Conpon.CateID ;
            parameters[6].Value = tbl_Conpon.Sale ;
            parameters[7].Value = tbl_Conpon.cDate;
            parameters[8].Value = tbl_Conpon.startDate ;
            parameters[9].Value = tbl_Conpon.endDate ;
            parameters[10].Value = tbl_Conpon.startDate_s ;
            parameters[11].Value = tbl_Conpon.endDate_s ;
           
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_Conpon", CommandType.StoredProcedure, parameters);
                tbl_Conpon.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_Conpon.ID = -1;
            }
            return tbl_Conpon.ID;
        }
     
        public bool Updatetbl_Conpon(tbl_Conpon tbl_Conpon)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{     
                                                                new SqlParameter("@conponID", SqlDbType.Int),
																new SqlParameter("@conponCode", SqlDbType.VarChar),
                                                                new SqlParameter("@conponName", SqlDbType.NVarChar),

                                                                new SqlParameter("@proId", SqlDbType.Int),
                                                                new SqlParameter("@partnerId", SqlDbType.Int),
                                                                new SqlParameter("@cateId", SqlDbType.Int),
															
                                                                new SqlParameter("@sale", SqlDbType.NVarChar),

																new SqlParameter("@cDate", SqlDbType.DateTime),
																new SqlParameter("@sDateDeal", SqlDbType.DateTime),
                                                                new SqlParameter("@eDateDeal", SqlDbType.DateTime),
                                                                new SqlParameter("@eDateDeal_s", SqlDbType.DateTime),
                                                                new SqlParameter("@eDateDeal_s", SqlDbType.DateTime),
															};
            parameters[0].Value = tbl_Conpon .ID;
            parameters[1].Value = tbl_Conpon.conponCode;
            parameters[2].Value = tbl_Conpon.conponName;
            parameters[3].Value = tbl_Conpon.proId;
            parameters[4].Value = tbl_Conpon.partnerId;
            parameters[5].Value = tbl_Conpon.CateID;
            parameters[6].Value = tbl_Conpon.Sale;
            parameters[7].Value = tbl_Conpon.cDate;
            parameters[8].Value = tbl_Conpon.startDate;
            parameters[9].Value = tbl_Conpon.endDate;
            parameters[10].Value = tbl_Conpon.startDate_s;
            parameters[11].Value = tbl_Conpon.endDate_s;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_Conpon", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

     
        public bool Deletetbl_Conpon(int id)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@conponID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_Conpon", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

     
        public tbl_Conpon Selecttbl_ConponByID(int id)
        {
            IDataReader dr;
            tbl_Conpon tbl_Conpon = new tbl_Conpon();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                dr = dataProvider.ExecuteReader("sp_Selecttbl_Conpon", CommandType.StoredProcedure, parameters);
                while (dr.Read())
                {
                    //tbl_Conpon.ID = Convert.ToInt32(dr["ID"].ToString());
                    //tbl_Conpon.CateID = dr["cateId"].ToString();
                    //tbl_Conpon.pId = Convert.ToInt32(dr["pId"].ToString());
                    //tbl_Conpon.cityId = Convert.ToInt32(dr["cityId"].ToString());
                    //tbl_Conpon.proName = (dr["proName"].ToString());
                    //tbl_Conpon.contact = (dr["contact"].ToString());
                    //tbl_Conpon.saleOff = dr["saleOff"].ToString();
                    //tbl_Conpon.price = Convert.ToDouble(dr["price"].ToString());
                    //tbl_Conpon.votes = Convert.ToInt32(dr["votes"].ToString());
                    //tbl_Conpon.voteSet = Convert.ToInt32(dr["voteSet"].ToString());
                    //tbl_Conpon.promotion = (dr["promotion"].ToString());
                    //tbl_Conpon.rank = Convert.ToInt32(dr["rank"].ToString());
                    //tbl_Conpon.poisition = (dr["poisition"].ToString());
                    //tbl_Conpon.Info = (dr["Info"].ToString());
                    //tbl_Conpon.timeFrame = (dr["timeFrame"].ToString());
                    //tbl_Conpon.cDate = Convert.ToDateTime(dr["cDate"].ToString());

                    tbl_Conpon.ID = Convert.ToInt32(dr["conponID"].ToString()) ;
                    tbl_Conpon.conponCode = dr["conponCode"].ToString();
                    tbl_Conpon.conponName= dr["conponName"].ToString();
                    tbl_Conpon.proId = Convert.ToInt32(dr["proID"].ToString());
                    tbl_Conpon.partnerId=Convert.ToInt32(dr["partnerId"].ToString());
                    tbl_Conpon.CateID = Convert.ToInt32(dr["cateId"].ToString());
                    tbl_Conpon.Sale=dr["sale"].ToString();
                    tbl_Conpon.cDate=Convert.ToDateTime(dr["cDate"].ToString());

                    if (dr["sDateDeal"].ToString() != string.Empty)
                    {
                        tbl_Conpon.startDate = Convert.ToDateTime(dr["sDateDeal"].ToString());
                    }
                    if (dr["eDateDeal"].ToString() != string.Empty)
                    {
                        tbl_Conpon.endDate = Convert.ToDateTime(dr["eDateDeal"].ToString());
                    }

                    if (dr["sDateDeal"].ToString() != string.Empty)
                    {
                        tbl_Conpon.startDate_s = Convert.ToDateTime(dr["sDateDeal_s"].ToString());
                    }
                    if (dr["eDateDeal_s"].ToString() != string.Empty)
                    {
                        tbl_Conpon.endDate_s = Convert.ToDateTime(dr["eDateDeal_s"].ToString());
                    }
                   
                }

            }
            catch (Exception ex)
            {
                return null;
            }
            return tbl_Conpon;
        }
        public DataTable Selecttbl_ConponsDynamic(string Where, string Order)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_ConponDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
        public DataTable Selecttbl_ConponsWithPageDynamic(int StartIndex, int MaxRecords, string Where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_ConponWithPagesDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
        //Cuong Code
        public static DataTable GetByPartnerIdAndBetween(int partnerid, int top, string date)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Conpon_GetByPartnerIdAndBetween", dbConn);
            dbCmd.Parameters.AddWithValue("@Top", top);
            dbCmd.Parameters.AddWithValue("@PartnerId", partnerid);
            dbCmd.Parameters.AddWithValue("@Date", date);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }


        public DataTable Statistic_SelectAllDealAndPaging(int StartIndex, int MaxRecords, string Where, string WhereOrder)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = {   new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  },
                                         new SqlParameter("@WhereConditionOrder",SqlDbType.NVarChar,500 ){Value = WhereOrder  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_statistic_SelectAllDealWithPaging", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        

        public static int GetTopSlale(int partnerId)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Conpon_GetTopSlale", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@PartnerId", partnerId);
            try
            {
                dbConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                object returnRole = dbCmd.ExecuteScalar();
                if (returnRole != null && returnRole != DBNull.Value)
                    retVal = ConvertUtility.ToInt32(returnRole);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static tbl_Conpon GetInfo(int iD)
        {
            tbl_Conpon retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Conpon_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@ID", iD);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read()) retVal = new tbl_Conpon(dr);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataTable Selecttbl_ConponTopDeal(int iSoLuong, string where, int iCateID)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            string sCommand = "";
            if (iCateID != 0)
            {
                if (iSoLuong == 0)
                    sCommand = "Select a.*, b.* from tblBaiViet a, tblDanhMuc b, tblBaiVietDanhMuc c where a.MaBaiViet = c.MaBaiViet and b.MaDanhMuc = c.MaDanhMuc and c.MaDanhMuc = " + iCateID + " " + where + " order by a.NgayTao desc";
                if (iSoLuong != 0)
                    sCommand = String.Format("Select Top {0} a.*, b.* from tblBaiViet a, tblDanhMuc b, tblBaiVietDanhMuc c where a.MaBaiViet = c.MaBaiViet and b.MaDanhMuc = c.MaDanhMuc and c.MaDanhMuc = {1} WHERE {2} order by a.NgayTao desc", iSoLuong, iCateID, where);
            }

            if (iCateID == 0)
            {
                sCommand = "Select * from tblBaiViet order by NgayTao desc";
                if (iSoLuong != 0)
                    sCommand = "Select Top " + iSoLuong + " * from tblBaiViet order by NgayTao desc";
            }

            try
            {
                ds = dataProvider.ExecuteDataSet(sCommand, CommandType.Text);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
    }
}
